<?php
//insurance zsn 20180102 10:56

class InsuranceOrderModel extends Model
{

    protected $tableName = 'insurance_order'; //重新定义表名

    public function getList($queryType, $inputs)
    {
        $model = M("", '', 'INS_DB_DSN');//跨库查询
        $p = $inputs["page"] ? $inputs["page"] : 1;
        $n = $inputs["rows"] ? $inputs["rows"] : 10;
        $limit = "limit " . ($p - 1) * $n . "," . $n;
        $sort = "";
        if ($inputs["sort"]) {
            $sort = "order by " . $inputs["sort"] . " " . $inputs["order"];
        }
        $where = '';
        $searchValue = $inputs["searchValue"] ? $inputs["searchValue"] : '';
        if ($searchValue != '车牌号/订单号/电话')
            $where .= ' and (iv.iv_plate_no like "%' . $searchValue . '%" or io.io_id like "%' . $searchValue . '%" or iv.iv_mobile like "%' . $searchValue . '%") ';

        if ($inputs['ia_id'] != '') {
            if ($inputs['ia_id'] == 'no') {
                $where .= ' and  io_type !=5 ';
            } else {
                $where .= ' and  ia_id = ' . $inputs['ia_id'];
            }
        }
        if ($inputs['order_type'] != '') {
            if ($inputs['order_type'] == '0') {//按天保保单
                $where .= ' and io.io_type in(1,2) ';
            } else {
                $where .= ' and io.io_type not in(1,2)  ';
            }
        }
        if ($inputs['order_status'] != '') {//>=3 and <=7未支付；8出单中；9已出单；10出单失败
            switch ($inputs['order_status']) {
                case 1:
                    $where .= ' and io.io_status >= 3 and io.io_status <= 7 ';
                    break;
                case 2:
                    $where .= ' and io.io_status = 8 ';
                    break;
                case 3:
                    $where .= ' and io.io_status = 9 ';
                    break;
                case 4:
                    $where .= ' and io.io_status = 10 ';
                    break;
            }
        }
        if ($inputs['order_duration'] != '') {
            switch ($inputs['order_duration']) {
                case '0'://自定义时间
                    $stime = $inputs['stime'];
                    $etime = $inputs['etime'];
                    $where .= ' and io.io_1st_update_time >= "' . $stime . '" and io.io_1st_update_time <= "' . $etime . '" ';
                    break;
                case '1'://近一天
                    $time = date("Y-m-d 00:00:00", strtotime("-1 day"));
                    $where .= ' and io.io_1st_update_time >= "' . $time . '" ';
                    break;
                case '3'://近三天
                    $time = date("Y-m-d 00:00:00", strtotime("-3 day"));
                    $where .= ' and io.io_1st_update_time >= "' . $time . '" ';
                    break;
                case '7'://近一周
                    $time = date("Y-m-d 00:00:00", strtotime("-1 week"));
                    $where .= ' and io.io_1st_update_time >= "' . $time . '" ';
                    break;
            }
        }

        $where .= ' and io_status >=3 and io_delete_status = 0 ';
        if ($queryType == 'count') {
            $fields = ' count(1) as count';
            $sql = "select " . $fields . " from `insurance_order` io
            join `insurance_vehicle` iv on io.io_ivid = iv.iv_id
           LEFT JOIN insurance_general  ON io_id =ig_ioid
           LEFT JOIN insurance_agent ON ig_userid=ia_userid
            LEFT JOIN insurance_sales_get_log ON isgl_ioid=io_id
           where 1=1 " . $where . ";";
        }
        if ($queryType == 'list') {
            $fields = ' io_id,
 io_order_no,
 iv_name_owner,
 iv_mobile,
 iv_plate_no,
 io_3rd_update_time,
 io_pay_amt,
 (case when io_status = 0 then "<font>生成订单</font>" when io_status = 1 then "<font>资料完善</font>" when io_status = 2 then "<font>资料确认</font>" when io_status = 3 then "<font>提交订单</font>" when io_status = 6 then "<font>提交购买申请</font>" when io_status = 9 then "<font color=blue>订单完成</font>" when io_status = 10 then "<font color=red>订单失败</font>" end) as status,
 io_status,
 io_type,
 (case  io_type when 1 then "<font>1 按天保设备</font>"
 when  2 then "<font>2 按天保盒子</font>"
 when  3 then "<font>3 八折优惠</font>"
 when  4 then "<font>4 自驾宝老用户</font>"
 when  5 then "<font>5 渠道代理</font>"
 when  6 then "<font>6 设备试用</font>"  end) as types,
 io_7th_update_time ';
            $sql = "select " . $fields . " from `insurance_order` io
             join `insurance_vehicle` iv on io.io_ivid = iv.iv_id
           LEFT JOIN insurance_general  ON io_id =ig_ioid
           LEFT JOIN insurance_agent ON ig_userid=ia_userid
            LEFT JOIN insurance_sales_get_log ON isgl_ioid=io_id
           where 1=1 " . $where . " group by io_id " . $sort . " " . $limit . ";";
        }
//        echo $sql;die;
        $result = $model->query($sql);
        return $result;
    }

    public function getExpList($queryType, $inputs)
    {
        $model = M("", '', 'INS_DB_DSN');//跨库查询
        $p = $inputs["page"] ? $inputs["page"] : 1;
        $n = $inputs["rows"] ? $inputs["rows"] : 10;
        $limit = "limit " . ($p - 1) * $n . "," . $n;
        $sort = "";
        if ($inputs["sort"]) {
            $sort = "order by " . $inputs["sort"] . " " . $inputs["order"];
        }
        $where = '';
        $searchValue = $inputs["searchValue"] ? $inputs["searchValue"] : '';
        if ($searchValue != '车牌号/电话')
            $where .= ' and (iv.iv_plate_no like "%' . $searchValue . '%" or iv.iv_mobile like "%' . $searchValue . '%") ';

        if ($inputs['order_type'] != '') {
            if ($inputs['order_type'] == '0') {//按天保保单
                $where .= ' and io.io_type in(1,2) ';
            } else {
                $where .= ' and io.io_type in(3,4,5) ';
            }
        }

        $where .= ' and io_status =9 and io_delete_status = 0 ';
        if ($queryType == 'count') {
            $fields = ' count(1) as count';
            $sql = "select " . $fields . " from `insurance_order` io
            join `insurance_vehicle` iv on io.io_ivid = iv.iv_id

            where 1=1 " . $where . ";";
        }
        if ($queryType == 'list') {
            $fields = ' io_policy_no,io_policy_no_traff,io_id,io_order_no,io_type,io_need_invoice,iv_name_owner,iv_mobile,iv_plate_no,io_3rd_update_time,io_pay_amt,io_status,io_6th_update_time,io_8th_update_time,io_submit_vhl_participant ';
            $sql = "select " . $fields . " from `insurance_order` io
             join `insurance_vehicle` iv on io.io_ivid = iv.iv_id where 1=1 " . $where . " " . $sort . " " . $limit . ";";
        }
//        echo $sql;
        $result = $model->query($sql);
        return $result;
    }

    public function getInfo($id)
    {
        $model = M("", '', 'INS_DB_DSN');//跨库查询
        $sql = "select
io_submit_vhl_participant,
io_order_no,
io_3rd_update_time,
io_type,
io_bindMobileNo,
io_status,
io_7th_update_time,
io_pay_amt,
iv_plate_no,
iv_vehicle_name,
iv_vin,
iv_engine_no,
iv_mid_year,
iv_reg_date,
iv_city_name,
io_t_insrnc_bgn_tm,
io_t_insrnc_end_tm,
io_t_traff_bgn_tm,
io_t_traff_end_tm,
io_2nd_get_result,
io_policy_no,
io_policy_no_traff,
io_tra_prm,
io_tax_prm,io_devi_goods
from `insurance_order` io join `insurance_vehicle` iv on io.io_ivid = iv.iv_id where io_id = " . $id . ";";
        return $model->query($sql);
    }

    public function getCheckAccountList($queryType, $inputs)
    {
        if ($inputs['kind'] == 'insurance') {

            $model = M("", '', 'INS_DB_DSN');//跨库查询
            $p = $inputs["page"] ? $inputs["page"] : 1;
            $n = ($inputs["rows"] ? $inputs["rows"] : 10);
            if($inputs['kind'] == 'insurance'){
                if($inputs['ins_or_tra'] != 3){
                    $n = ($inputs["rows"] ? $inputs["rows"] : 10)/2;
                    if($inputs['type_insurance'] != 'all')
                        $n=$n*2;
                }
            }

            if($inputs['ins_or_tra'] != 3)
                $limit = "limit " . ($p - 1) * $n . "," . $n;
            $sort = "";
            if ($inputs["sort"]) {
                $sort = "order by " . $inputs["sort"] . " " . $inputs["order"];
            }
            $where = '';
            if ($inputs['stime']) {
                $stime = $inputs['stime'];
                $where .= ' and io.io_8th_update_time >= "' . $stime . '" ';
            }

            if ($inputs['etime']) {
                $etime = $inputs['etime'] . " 23:59:59";
                $where .= ' and io.io_8th_update_time <= "' . $etime . '" ';
            }
            $where .= ' and  io_status =9 and io_delete_status = 0  and (ia_status <>-1 or ia_status is null)';
            if ($queryType == 'count') {
                $fields = ' count(1) as count';
                $sql = "select " . $fields . " from `insurance_order` io
                join `insurance_vehicle` iv on io_ivid = iv_id
                LEFT JOIN insurance_general  ON io_id =ig_ioid
                LEFT JOIN insurance_agent ON ig_userid=ia_userid
                where 1=1   " . $where . ";";
            }
//            echo $sql;
            if ($queryType == 'list') {
                if($inputs['ins_or_tra'] == 1){
                    $where .=' and (io_type1sum+io_type2sum) > 0 ';
                    $fields = ' io_submit_vhl_participant,iv_id,ia_userid,io_id,io_policy_no,    iv_name_owner,     iv_mobile,     iv_plate_no,    io_t_insrnc_bgn_tm ins_bgn_tm,     io_type1sum+io_type2sum io_pay_amt,     io_8th_update_time ';
                    $sql = "select " . $fields . " from `insurance_order` io
                     join `insurance_vehicle` iv on io_ivid = iv_id
                   LEFT JOIN insurance_general  ON io_id =ig_ioid
                   LEFT JOIN insurance_agent ON ig_userid=ia_userid
                   where 1=1   " . $where . " " . $sort . " " . $limit . ";";
                }elseif($inputs['ins_or_tra'] == 2){

                    $where .=' and (io_type3sum) > 0 ';
                    $fields = 'io_submit_vhl_participant, io_id,io_policy_no_traff io_policy_no,    iv_name_owner,     iv_mobile,     iv_plate_no,    io_t_traff_bgn_tm ins_bgn_tm,   io_type3sum  io_pay_amt,     io_8th_update_time ';
                    $sql = "select " . $fields . " from `insurance_order` io
                     join `insurance_vehicle` iv on io.io_ivid = iv.iv_id
                   LEFT JOIN insurance_general  ON io_id =ig_ioid
                   LEFT JOIN insurance_agent ON ig_userid=ia_userid
                   where 1=1   " . $where . " " . $sort . " " . $limit . ";";
                }elseif($inputs['ins_or_tra'] == 21){

                    $where .=' and (io_type3sum) > 0 ';
                    $fields = 'io_submit_vhl_participant, io_id,io_policy_no_traff io_policy_no,    iv_name_owner,     iv_mobile,     iv_plate_no,   io_t_traff_bgn_tm ins_bgn_tm,    io_tra_prm  io_pay_amt,     io_8th_update_time ';
                    $sql = "select " . $fields . " from `insurance_order` io
                     join `insurance_vehicle` iv on io.io_ivid = iv.iv_id
                   LEFT JOIN insurance_general  ON io_id =ig_ioid
                   LEFT JOIN insurance_agent ON ig_userid=ia_userid
                   where 1=1   " . $where . " " . $sort . " " . $limit . ";";
                }else{ //图表数据
                    $fields = '   LEFT (io_8th_update_time,10) time ,SUM(io_pay_amt)  money ';
                    $sql = "select " . $fields . " from `insurance_order` io
                     join `insurance_vehicle` iv on io.io_ivid = iv.iv_id
                   LEFT JOIN insurance_general  ON io_id =ig_ioid
                   LEFT JOIN insurance_agent ON ig_userid=ia_userid
                   where 1=1   " . $where . " GROUP BY time " . $sort . " " . $limit . ";";
                }
            }
//            echo $sql;
            $result = $model->query($sql);
            return $result;

        } elseif ($inputs['kind'] == 'channel') { //渠道
//            echo 'channel';
            $model = M("", '', 'INS_DB_DSN');//跨库查询
            $p = $inputs["page"] ? $inputs["page"] : 1;
            $n = $inputs["rows"] ? $inputs["rows"] : 10;
            $limit = "limit " . ($p - 1) * $n . "," . $n;
            $sort = "";
            if ($inputs["sort"]) {
                $sort = "order by " . $inputs["sort"] . " " . $inputs["order"];
            }
            $where = '';
            $where .= ' and ia_status <>-1 ';
            if ($inputs['stime']) {
                $stime = $inputs['stime'];
                $where .= ' and io_8th_update_time >= "' . $stime . '" ';
            }

            if ($inputs['etime']) {
                $etime = $inputs['etime'] . " 23:59:59";
                $where .= ' and io_8th_update_time <= "' . $etime . '" ';
            }

            if ($inputs['channel'] != 'all') {
                if($inputs['channel'] == '3'){
                    $sql = "SELECT    count(1)     FROM `insurance_order` io
             JOIN `insurance_vehicle` iv ON io_ivid = iv_id
           LEFT JOIN insurance.insurance_general  ON io_id =ig_ioid
           LEFT JOIN insurance_agent ON ig_userid=ia_userid
           LEFT JOIN insurance_user_commission ON iuc_ioid=io_id
            WHERE io_type !=5 AND io_status=9   AND io_delete_status = 0 ;";

//                    echo $sql;
                    $result =   $model->query( $sql);
                    return   $result;
                }
//            }
                if ($inputs['channel_user'] != 'all') {
                    $where .= ' and ( ia_pid = "' . $inputs['channel_user'] . '" or ia_userid = "'.$inputs['channel_user'].'") ';
                }
                if ($inputs['child_channel_user'] != 'all' && $inputs['child_channel_user'] !='') {
                    $where .= ' and ia_userid = "' . $inputs['child_channel_user'] . '" ';
                }
                $where .= ' and ia_class = "' . $inputs['channel'] . '" ';
            }
                $group = " GROUP BY ia_mobile ";
            if ($queryType == 'count') {
                $fields = " count(1) as count  ,ia_mobile,IF(ia_pid >0,CONCAT(ia_channel_name,' (',ia_name,')'),ia_name) ia_name,ia_channel_name,iv_name_owner,io_id ,iv_id,ia_pid";
                $sql = "select count(1) count from (select " . $fields . " from   insurance_order
                  LEFT JOIN insurance_vehicle    ON io_ivid = iv_id
                  LEFT JOIN insurance_general  ON io_id =ig_ioid
                  LEFT JOIN insurance_agent ON ig_userid=ia_userid
                   WHERE  1=1     AND ia_mobile IS NOT NULL      " . $where . " ".$group."  )qq ;";
            }
            if ($queryType == 'list') {
                $fields = " iv_id,ia_mobile, IF(ia_pid >0,CONCAT(ia_channel_name,' (',ia_name,')'),ia_name) ia_name,io_id,ia_userid ";
                $sql = "select " . $fields . " FROM (select  * from  insurance_order
                  LEFT JOIN insurance_vehicle    ON io_ivid = iv_id
                  LEFT JOIN insurance_general  ON io_id =ig_ioid
                  LEFT JOIN insurance_agent ON ig_userid=ia_userid
                   WHERE 1=1             AND ia_mobile IS NOT NULL           " . $where .") qq  ". $group . $sort . " " . $limit . ";";
            }
//            echo $sql;
            $result = $model->query($sql);
            return $result;

        }
    }

    public function getAllCheckAccountList($queryType, $inputs)
    {  //导出所有订单
//        var_dump($inputs);
        if ($inputs['kind'] == 'insurance') {

            $model = M("", '', 'INS_DB_DSN');//跨库查询
            if ($inputs['type_insurance'] != 'all') {
            }
            if ($inputs['stime']) {
                $stime = $inputs['stime'];
                $where .= ' and io.io_8th_update_time >= "' . $stime . '" ';
            }

            if ($inputs['etime']) {
                $etime = $inputs['etime'] . " 23:59:59";
                $where .= ' and io.io_8th_update_time <= "' . $etime . '" ';
            }
            $where .= ' and io_status =9 and io_delete_status = 0 ';
            if ($queryType == 'list') {
                if($inputs['ins_or_tra'] == 1){
                    $where .=' and (io_type1sum+io_type2sum) > 0 ';
                    $fields = "   io_submit_vhl_participant, io_8th_update_time,    io_policy_no,    IF(ia_channel_name != '',IF(ia_pid > 0,CONCAT(ia_channel_name,' (',`ia_name`,')'),ia_name),CONCAT(ipc_shop_name,'(',isgl_cid,')'))  ipc_shop_name,    iv_name_owner,  io_t_insrnc_bgn_tm ins_bgn_tm,    iv_mobile,    iv_plate_no,    io_type1sum+io_type2sum  io_pay_amt    ";
                }elseif($inputs['ins_or_tra'] == 2){
                    $where .=' and (io_type3sum) > 0 ';
                    $fields = "   io_submit_vhl_participant, io_8th_update_time,    io_policy_no_traff io_policy_no,    IF(ia_channel_name != '',IF(ia_pid > 0,CONCAT(ia_channel_name,' (',`ia_name`,')'),ia_name),CONCAT(ipc_shop_name,'(',isgl_cid,')'))  ipc_shop_name,    iv_name_owner,  io_t_traff_bgn_tm ins_bgn_tm,    iv_mobile,    iv_plate_no,   io_type3sum  io_pay_amt    ";
                }else{
                    $where .=' and (io_type3sum) > 0 ';
                    $fields = "   io_submit_vhl_participant, io_8th_update_time,    io_policy_no_traff io_policy_no,   IF(ia_channel_name != '',IF(ia_pid > 0,CONCAT(ia_channel_name,' (',`ia_name`,')'),ia_name),CONCAT(ipc_shop_name,'(',isgl_cid,')'))   ipc_shop_name,    iv_name_owner,   io_t_traff_bgn_tm ins_bgn_tm,  iv_mobile,    iv_plate_no,      io_tra_prm  io_pay_amt    ";
                }
                $sql = "select " . $fields . " from `insurance_order` io
                 join `insurance_vehicle` iv on io.io_ivid = iv.iv_id
               LEFT JOIN insurance_general  ON io_id =ig_ioid
               LEFT JOIN insurance_agent ON ig_userid=ia_userid
                LEFT JOIN insurance_sales_get_log ON isgl_ioid=io_id
                left join insurance_pub_channel on isgl_cid = ipc_id
               where 1=1 " . $where . "   order by io_8th_update_time desc;";
            }
//            echo $sql;
            $result = $model->query($sql);
            return $result;
        } elseif ($inputs['kind'] == 'channel') { //渠道
            $model = M("", '', 'INS_DB_DSN');//跨库查询
            $where = '';
            $where .= ' and ia_status <>-1 ';
//            print_r($inputs);die;
            if ($inputs['channel'] != 'all' && $inputs['channel'] != '3') {
                $where .= ' and ia_class = "' . $inputs['channel'] . '" ';
            }
            if ($inputs['channel_user'] != 'all' && $inputs['channel_user'] != 'undefined') {
                $where .= ' and (ig_userid = "' . $inputs['channel_user'] . '" or ia_pid = "'.$inputs['channel_user'].'") ';
            }
            if ($inputs['child_channel_user'] != 'all' && $inputs['child_channel_user'] != 'undefined') {
                $where .= ' and ia_userid = "' . $inputs['child_channel_user'] . '" ';
            }
            if ($inputs['stime']) {
                $stime = $inputs['stime'];
                $where .= ' and io_8th_update_time >= "' . $stime . '" ';
            }
            if ($inputs['etime']) {
                $etime = $inputs['etime'] . " 23:59:59";
                $where .= ' and io_8th_update_time <= "' . $etime . '" ';
            }
            if ($queryType == 'count') {
                $fields = ' count(1) as count ';
                $sql = "select count(1) count from (select " . $fields . " from   insurance_order
              LEFT JOIN insurance_general  ON io_id =ig_ioid
              LEFT JOIN insurance_agent ON ig_userid=ia_userid
               WHERE  io_status=9  AND io_delete_status = 0
               " . $where . "  AND iuc_isgiveto_p = 0 ) qq;";
            }
            $fields = " io_8th_update_time,IF(ia_pid >0,CONCAT(ia_channel_name,' (',ia_name,')'),ia_name)  ia_name,iv_name_owner,io_bindMobileNo, iv_plate_no,
            io_pay_amt,io_type1sum+io_type2sum io_type12sum,
            io_type3sum,io_tra_prm,
            CONCAT(ROUND((io_type1sum+io_type2sum) *iuc_busi_rate,2),'（',iuc_busi_rate,'）') ins,
            CONCAT(ROUND((io_type3sum) *iuc_tra_rate,2),'（',iuc_tra_rate,'）') tra";
            if($queryType == 'channel_list'){
                $sql = "select " . $fields . "   FROM `insurance_order` io
             JOIN `insurance_vehicle` iv ON io_ivid = iv_id
           LEFT JOIN insurance_general  ON io_id =ig_ioid
           LEFT JOIN insurance_agent ON ig_userid=ia_userid
           LEFT JOIN insurance_user_commission ON iuc_ioid=io_id
            WHERE io_type !=5 and   io_status=9   AND io_delete_status = 0
               " . $where . "   GROUP BY io_id   order by io_8th_update_time desc ;";
            }

            if ($queryType == 'list') {
//                if($inputs['channel'] == 'all' )
                $sql = "select " . $fields . "   FROM `insurance_order` io
             JOIN `insurance_vehicle` iv ON io_ivid = iv_id
           LEFT JOIN insurance_general  ON io_id =ig_ioid
           LEFT JOIN insurance_agent ON ig_userid=ia_userid
           LEFT JOIN insurance_user_commission ON iuc_ioid=io_id
            WHERE io_type=5 and   io_status=9   AND io_delete_status = 0
               " . $where . "   GROUP BY io_id   order by io_8th_update_time desc ;";
            }
//            echo $sql;
            $result = $model->query($sql);
            return $result;
        }
    }

    public function getChannelDetailsList($queryType, $inputs)
    { //导出渠道详情列
        $model = M("", '', 'INS_DB_DSN');//跨库查询
        $p = $inputs["page"] ? $inputs["page"] : 1;
        $n = $inputs["rows"] ? $inputs["rows"] : 10;
        $limit = "limit " . ($p - 1) * $n . "," . $n;
        $sort = "";
        if ($inputs["sort"]) {
            $sort = "order by " . $inputs["sort"] . " " . $inputs["order"];
        }
        $where = '';
        $where .= ' and ia_status <>-1 ';
        if ($inputs['stime']) {
            $stime = $inputs['stime'];
            $where .= ' and io_8th_update_time >= "' . $stime . '" ';
        }
//        dump($inputs);
        if ($inputs['etime']) {
            $etime = $inputs['etime'] . " 23:59:59";
            $where .= ' and io_8th_update_time <= "' . $etime . '" ';
        }
//        dump($inputs);die;
        if($inputs['cl'] != '3'){
            if($inputs['id'] !='all' && $inputs['id'] !='undefined' && $inputs['id'] !='')
                $where .= ' and ia_userid = '.$inputs['id'] .' ';
            if($inputs['op'] == 'out'){
                if($inputs['child_channel_user'] != 'all' &&$inputs['child_channel_user'] !='undefined')
                    $where .= ' and ia_userid ='.$inputs['child_channel_user'] .' ';
            }else{
                if($inputs['child_iv_id'] != 'all' &&$inputs['child_iv_id'] !='undefined'){
//                    $where .= ' and iv_id ='.$inputs['child_iv_id'] .' ';
                    $where .= ' and ia_userid ='.$inputs['child_iv_id'] .' ';
                }
            }
        }
        if ($queryType == 'count') {
            $fields = ' count(1) count ';
//                dump($inputs);
            if($inputs['cl'] == '3'){
                $sql="select ".$fields." from (SELECT  COUNT(1)          FROM `insurance_order` io             JOIN `insurance_vehicle` iv ON io_ivid = iv_id           LEFT JOIN insurance.insurance_general  ON io_id =ig_ioid           LEFT JOIN insurance_agent ON ig_userid=ia_userid           LEFT JOIN insurance_user_commission ON iuc_ioid=io_id            WHERE io_type !=5 AND io_status=9   AND io_delete_status = 0 ".$where." GROUP BY io_id) qq ;";
            }else{
                if($inputs['id'] == 'undefined')
                    $sql="select ".$fields." from (SELECT  COUNT(1)          FROM `insurance_order` io             JOIN `insurance_vehicle` iv ON io_ivid = iv_id           LEFT JOIN insurance.insurance_general  ON io_id =ig_ioid           LEFT JOIN insurance_agent ON ig_userid=ia_userid           LEFT JOIN insurance_user_commission ON iuc_ioid=io_id            WHERE io_type !=5 AND io_status=9   AND io_delete_status = 0 ".$where."  GROUP BY io_id) qq ;";
                else
                    $sql = "select count(1) count from (select " . $fields . " FROM `insurance_order` io JOIN `insurance_vehicle` iv ON io_ivid = iv_id LEFT JOIN insurance_general ON io_id =ig_ioid LEFT JOIN insurance_agent ON ig_userid=ia_userid LEFT JOIN insurance_user_commission ON iuc_ioid=io_id WHERE io_type =5 AND io_status=9 AND io_delete_status = 0  " . $where . " GROUP BY io_id ) qq;";
            }
        }
        if($inputs['cl'] == '3'){
            $where .= ' and io_type !=5 ';
        }else{
            if($inputs['id'] != 'undefined' )
                $where .= ' and io_type =5 ';
            else
                $where .= ' and io_type !=5 ';
        }
            if ($queryType == 'list') {
                $fields = " io_8th_update_time,IF(ia_pid >0,CONCAT(ia_channel_name,' (',ia_name,')'),ia_name) ia_name,iv_name_owner,io_bindMobileNo, iv_plate_no,
        io_pay_amt,io_type1sum+io_type2sum io_type12sum,io_type3sum,io_tra_prm ,
        CONCAT(ROUND((io_type1sum+io_type2sum) *iuc_busi_rate,2),'（',iuc_busi_rate,'）') ins,
        CONCAT(ROUND((io_tra_prm) *iuc_tra_rate,2),'（',iuc_tra_rate,'）') tra";
                $sql = "select " . $fields . "   FROM `insurance_order` io
        JOIN `insurance_vehicle` iv ON io_ivid = iv_id
        LEFT JOIN insurance_general  ON io_id =ig_ioid
        LEFT JOIN insurance_agent ON ig_userid=ia_userid
        LEFT JOIN insurance_user_commission ON iuc_ioid=io_id
        WHERE 1=1  AND io_status=9   AND io_delete_status = 0
               " . $where . " GROUP BY io_id " . $sort . " " . $limit . ";";
            }
//        echo  $sql;
        $result = $model->query($sql);
        return $result;
    }
    public function getOutChannelDetailsList($queryType, $inputs)
    { //导出渠道详情列
        $model = M("", '', 'INS_DB_DSN');//跨库查询
        $where = '';
        $where .= ' and ia_status <>-1 ';
        if ($inputs['stime']) {
            $stime = $inputs['stime'];
            $where .= ' and io_8th_update_time >= "' . $stime . '" ';
        }
        if ($inputs['etime']) {
            $etime = $inputs['etime'] . " 23:59:59";
            $where .= ' and io_8th_update_time <= "' . $etime . '" ';
        }
        if($inputs['id'] != 'undefined' && $inputs['id'])
            $where .= ' and ia_userid = '.$inputs['id'] .' ';
//        if($inputs['channel_user'] != 'all' &&$inputs['channel_user'] !='undefined')
////            $where .= ' and ig_userid ='.$inputs['channel_user'] .' ';
//        if ($inputs['child_channel_user'] != 'all' &&$inputs['child_channel_user'] !='undefined') {
////            $where .= ' and iv_id = "' . $inputs['child_channel_user'] . '" ';
//        }
            $limit = '';
            if ($queryType == 'count') {
                $fields = ' count(1) count ';
                if($inputs['cl'] == '3'){
                    $sql="select ".$fields." from (SELECT  COUNT(1)          FROM `insurance_order` io             JOIN `insurance_vehicle` iv ON io_ivid = iv_id           LEFT JOIN insurance.insurance_general  ON io_id =ig_ioid           LEFT JOIN insurance_agent ON ig_userid=ia_userid           LEFT JOIN insurance_user_commission ON iuc_ioid=io_id            WHERE io_type !=5 AND io_status=9   AND io_delete_status = 0  GROUP BY io_id) qq ;";
                }else if($inputs['channel'] == '3'){
//                    $sql="select ".$fields." from (SELECT  COUNT(1)          FROM `insurance_order` io             JOIN `insurance_vehicle` iv ON io_ivid = iv_id           LEFT JOIN insurance.insurance_general  ON io_id =ig_ioid           LEFT JOIN insurance_agent ON ig_userid=ia_userid           LEFT JOIN insurance_user_commission ON iuc_ioid=io_id            WHERE io_type !=5 AND io_status=9   AND io_delete_status = 0  GROUP BY io_id) qq ;";

                }else{
                    if($inputs['id'] == 'undefined')
                        $sql="select ".$fields." from (SELECT  COUNT(1)          FROM `insurance_order` io             JOIN `insurance_vehicle` iv ON io_ivid = iv_id           LEFT JOIN insurance.insurance_general  ON io_id =ig_ioid           LEFT JOIN insurance_agent ON ig_userid=ia_userid           LEFT JOIN insurance_user_commission ON iuc_ioid=io_id            WHERE io_type !=5 AND io_status=9   AND io_delete_status = 0  GROUP BY io_id) qq ;";
                    else
                        $sql = "select count(1) count from (select " . $fields . " FROM `insurance_order` io JOIN `insurance_vehicle` iv ON io_ivid = iv_id LEFT JOIN insurance_general ON io_id =ig_ioid LEFT JOIN insurance_agent ON ig_userid=ia_userid LEFT JOIN insurance_user_commission ON iuc_ioid=io_id WHERE io_type =5 AND io_status=9 AND io_delete_status = 0  " . $where . " GROUP BY io_id ) qq;";
                }
            }
            if ($queryType == 'list') {
                if($inputs['channel'] == '3'   || $inputs['channel'] == 'all'){
                    $sql = ("SELECT    io_8th_update_time, IF(ia_pid > 0,CONCAT(ia_channel_name,' (',ia_name,')'),ia_name) ia_name,iv_name_owner,  io_bindMobileNo,  iv_plate_no,  io_pay_amt,  io_type1sum + io_type2sum io_type12sum, io_type3sum,io_tra_prm ,
                            ROUND((io_type1sum + io_type2sum) * iuc_busi_rate,2) ins,iuc_busi_rate,
                            ROUND((io_tra_prm) * iuc_tra_rate, 2) tra  ,iuc_tra_rate,
                            ROUND(((io_type1sum + io_type2sum) * iuc_busi_rate + io_tra_prm * iuc_tra_rate),2) total FROM  insurance_order io
                            JOIN `insurance_vehicle` iv    ON io_ivid = iv_id  LEFT JOIN insurance.insurance_general    ON io_id = ig_ioid  LEFT JOIN insurance_agent    ON ig_userid = ia_userid  LEFT JOIN insurance_user_commission    ON iuc_ioid = io_id
                            WHERE io_type != 5  AND io_status = 9  AND io_delete_status = 0 ".$where." GROUP BY io_id  " . $sort . " " . $limit . ";");
                    if($inputs['id'] == 'undefined' || $inputs['id'])
                        $sql = ("SELECT    io_8th_update_time, IF(ia_pid > 0,CONCAT(ia_channel_name,' (',ia_name,')'),ia_name) ia_name,iv_name_owner,  io_bindMobileNo,  iv_plate_no,  io_pay_amt,  io_type1sum +
                            ROUND((io_type1sum + io_type2sum) * iuc_busi_rate,2) ins,iuc_busi_rate,
                            ROUND((io_tra_prm) * iuc_tra_rate, 2) tra  ,iuc_tra_rate,
                            ROUND(((io_type1sum + io_type2sum) * iuc_busi_rate + io_tra_prm * iuc_tra_rate),2) total FROM  insurance_order io
                            JOIN `insurance_vehicle` iv    ON io_ivid = iv_id  LEFT JOIN insurance.insurance_general    ON io_id = ig_ioid  LEFT JOIN insurance_agent    ON ig_userid = ia_userid  LEFT JOIN insurance_user_commission    ON iuc_ioid = io_id
                            WHERE io_type = 5  AND io_status = 9  AND io_delete_status = 0 ".$where." GROUP BY io_id  " . $sort . " " . $limit . ";");
                }else{
                        $fields = " io_8th_update_time,IF(ia_pid > 0,CONCAT(ia_channel_name,' (',ia_name,')'),ia_name) ia_name,iv_name_owner,io_bindMobileNo, iv_plate_no,
                io_pay_amt,io_type1sum+io_type2sum io_type12sum, io_type3sum,io_tra_prm ,
                ROUND((io_type1sum + io_type2sum) * iuc_busi_rate,2) ins,iuc_busi_rate,
                ROUND((io_tra_prm) * iuc_tra_rate, 2) tra  ,iuc_tra_rate,
                ROUND(((io_type1sum + io_type2sum) * iuc_busi_rate + io_tra_prm * iuc_tra_rate),2) total";
                        $sql = "select " . $fields . "   FROM `insurance_order` io
                JOIN `insurance_vehicle` iv ON io_ivid = iv_id
                LEFT JOIN insurance_general  ON io_id =ig_ioid
                LEFT JOIN insurance_agent ON ig_userid=ia_userid
                LEFT JOIN insurance_user_commission ON iuc_ioid=io_id
                WHERE io_type =5 AND io_status=9   AND io_delete_status = 0
                       " . $where . " GROUP BY io_id " . $sort . " " . $limit . ";";
                }
            }
//        }
//                    echo $sql;
        $result = $model->query($sql);
//        dump($model->getLastSql());
//die;
//        dump($result);
        return $result;
    }

}
